<#assign ___versionMajor = 1>
<#assign ___versionMinor = 0>
<#assign ___versionMicro = 1>

<#if (sqlUtils.isMajor(1,0,1))>
		<#assign metadata = sqlUtils.loadMetadata('APP')>
		<#assign sql = 'alter table app.key_definition add constraint uni_name_keydef unique(name, entity_type_id)'>
		<#assign addConstraint = sqlUtils.execute(sql)>
		<#if !addConstraint >
			ERROR: CAN'T EXECUTE SQL COMMAND: 
			${sql} 
			DESCRIPTION: 
			${sqlUtils.getLastError()} 
			ERROR KEY: 
			${sqlUtils.getLastKeyError()} 
		</#if>
		<#if !sqlUtils.hasColumn('ENTITY_TYPE', 'EDITFILE_CLASS')>
			<#assign sql = 'alter table app.entity_type add column editfile_class varchar(255)'>
			<#if sqlUtils.execute(sql)>
				<#assign sql = 'update app.entity_type set editfile_class = \'org.mags.remas.editfile.FreeMarkerEditFile\' 
				where code = \'TEMP\''>
				<#assign updated = sqlUtils.executeUpdate(sql)>
				<#if (updated<0) >
					ERROR: CAN'T EXECUTE SQL COMMAND: 
					${sql} 
					DESCRIPTION: 
					${sqlUtils.getLastError()} 
					ERROR KEY: 
					${sqlUtils.getLastKeyError()} 
				</#if>
				<#assign sql = 'update app.entity_type set editfile_class = \'org.mags.remas.editfile.HtmlEditFile\' 
				, process_class = \'org.mags.remas.process.FreeMarkerProcess\', preview_class = \'org.mags.remas.preview.HtmlPreview\'  
				where code = \'DOC\''>
				<#assign updated = sqlUtils.executeUpdate(sql)>
				<#if (updated<0) >
					ERROR: CAN'T EXECUTE SQL COMMAND: 
					${sql} 
					DESCRIPTION: 
					${sqlUtils.getLastError()} 
					ERROR KEY: 
					${sqlUtils.getLastKeyError()} 
				</#if>
			<#else>
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			</#if>	 
		</#if>
		<#if !sqlUtils.hasColumn('ENTITY_FILE', 'EXTENSION')>
			<#assign sql = 'alter table app.entity_file add column extension varchar(5)'>
			<#if sqlUtils.execute(sql)>
			</#if>	 
		</#if>
		<#if !sqlUtils.hasColumn('ENTITY_TYPE', 'EDIT_CLASS')>
			<#assign sql = 'alter table app.entity_type add column edit_class varchar(255)'>
			<#if !sqlUtils.execute(sql)>
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			</#if>	 
		</#if>
		<#if !sqlUtils.hasColumn('ENTITY_TYPE', 'CREATE_CLASS')>
			<#assign sql = 'alter table app.entity_type add column create_class varchar(255)'>
			<#if !sqlUtils.execute(sql)>
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			</#if>	 
		</#if>
		<#if !sqlUtils.hasColumn('ENTITY_TYPE', 'DELETE_CLASS')>
			<#assign sql = 'alter table app.entity_type add column delete_class varchar(255)'>
			<#if !sqlUtils.execute(sql)>
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			</#if>	 
		</#if>
		<#if !sqlUtils.hasColumn('ENTITY_TYPE', 'FIND_CLASS')>
			<#assign sql = 'alter table app.entity_type add column find_class varchar(255)'>
			<#if !sqlUtils.execute(sql)>
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			</#if>	 
		</#if>
		<#if !sqlUtils.hasColumn('ENTITY_TYPE', 'WINDOW_CLASS')>
			<#assign sql = 'alter table app.entity_type add column window_class varchar(255)'>
			<#if !sqlUtils.execute(sql)>
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			</#if>	 
		</#if>		
		<#assign sql = 'select count(*) from app.entity_type et, app.entity_category ec where 
		ec.code = \'REQU\' and et.code = \'PAGEBREAK\' '>
		<#assign count = sqlUtils.executeCount(sql)>
		<#if (count<0)>
			ERROR: CAN'T EXECUTE SQL COMMAND: 
			${sql} 
			DESCRIPTION: 
			${sqlUtils.getLastError()} 
			ERROR KEY: 
			${sqlUtils.getLastKeyError()} 
		</#if>
		<#if count==0>
			<#assign sql = 'insert into app.entity_type(created_by, updated_by, name, code, entity_category_id)
			select \'remas\', \'remas\', \'page break\', \'PAGEBREAK\', entity_category_id from
			app.entity_category where code = \'REQU\' '>
			<#assign count = sqlUtils.executeUpdate(sql)>
			<#if (count<0) >
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			</#if>
			<#if count==1>
				<#assign sql = 'insert into app.entity_structure(created_by, updated_by, entity_type_id, entity_type_parent_id, view_type_id)
				select \'remas\', \'remas\', e.entity_type_id, p.entity_type_id, v.view_type_id 
				from app.entity_type e, app.entity_type p, app.view_type v, app.entity_category c where e.entity_category_id = c.entity_category_id and p.entity_category_id = c.entity_category_id 
				and c.code=\'REQU\' and v.code = \'TREEVIEW\' and  e.code = \'PAGEBREAK\' and p.code = \'REQFOL\''>
				<#assign count = sqlUtils.executeUpdate(sql)>
				<#if (count<0) >
					ERROR: CAN'T EXECUTE SQL COMMAND: 
					${sql} 
					DESCRIPTION: 
					${sqlUtils.getLastError()} 
					ERROR KEY: 
					${sqlUtils.getLastKeyError()} 
				</#if>		
			</#if>
		</#if>
		
		
		
		<#assign sql = 'select count(*) from app.entity_type et, app.entity_category ec where 
		ec.code = \'REQU\' and et.code = \'IMAGE\' '>
		<#assign count = sqlUtils.executeCount(sql)>
		<#if (count<0)>
			ERROR: CAN'T EXECUTE SQL COMMAND: 
			${sql} 
			DESCRIPTION: 
			${sqlUtils.getLastError()} 
			ERROR KEY: 
			${sqlUtils.getLastKeyError()} 
		</#if>
		<#if count==0>
			<#assign sql = 'insert into app.entity_type(created_by, updated_by, name, code, entity_category_id, editfile_class, preview_class, default_action)
			select \'remas\', \'remas\', \'image\', \'IMAGE\', entity_category_id, \'org.mags.remas.editfile.ImageEditFile\', \'org.mags.remas.preview.ImagePreview\', \'preview\' from
			app.entity_category where code = \'REQU\''>
			<#assign count = sqlUtils.executeUpdate(sql)>
			<#if (count<0) >
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			</#if>
			<#if count==1>
				<#assign sql = 'insert into app.entity_structure(created_by, updated_by, entity_type_id, entity_type_parent_id, view_type_id)
				select \'remas\', \'remas\', e.entity_type_id, p.entity_type_id, v.view_type_id 
				from app.entity_type e, app.entity_type p, app.view_type v, app.entity_category c where e.entity_category_id = c.entity_category_id and p.entity_category_id = c.entity_category_id 
				and c.code=\'REQU\' and v.code = \'TREEVIEW\' and  e.code = \'IMAGE\' and p.code = \'REQFOL\''>
				<#assign count = sqlUtils.executeUpdate(sql)>
				<#if (count<0) >
					ERROR: CAN'T EXECUTE SQL COMMAND: 
					${sql}  
					DESCRIPTION: 
					${sqlUtils.getLastError()} 
					ERROR KEY: 
					${sqlUtils.getLastKeyError()} 
				<#else>
					<#assign sql = 'insert into app.key_definition(created_by, updated_by, entity_type_id, key_type_id, name, allow_empty, default_value)
					select \'remas\', \'remas\', entity_type_id, key_type_id, \'Width\', \'N\', \'0\'
					from app.entity_type et, app.key_type kt where et.code = \'IMAGE\' and kt.name = \'INTEGER\' '>
					<#assign count = sqlUtils.executeUpdate(sql)>
					<#if (count<0) >
						ERROR: CAN'T EXECUTE SQL COMMAND: 
						${sql}  
						DESCRIPTION: 
						${sqlUtils.getLastError()} 
						ERROR KEY: 
						${sqlUtils.getLastKeyError()} 
					</#if>			
					<#assign sql = 'insert into app.key_definition(created_by, updated_by, entity_type_id, key_type_id, name, allow_empty, default_value)
					select \'remas\', \'remas\', entity_type_id, key_type_id, \'Height\', \'N\', \'0\'
					from app.entity_type et, app.key_type kt where et.code = \'IMAGE\' and kt.name = \'INTEGER\' '>
					<#assign count = sqlUtils.executeUpdate(sql)>
					<#if (count<0) >
						ERROR: CAN'T EXECUTE SQL COMMAND: 
						${sql} 
						DESCRIPTION: 
						${sqlUtils.getLastError()} 
						ERROR KEY: 
						${sqlUtils.getLastKeyError()} 
					</#if>			
				</#if>		
			</#if>
		</#if>
		
		<#assign sql = 'select count(*) from app.entity e inner join app.entity_file ef on (e.entity_id=ef.entity_id) where e.code in(\'TEMP-0001\',\'TEMP-0002\',\'TEMP-0003\',\'TEMP-0004\',\'TEMP-0005\',\'TEMP-0006\',\'TEMP-0007\',\'TEMP-0008\')'>
		<#assign size = sqlUtils.executeCount(sql)>
		<#if (size<0)>
			ERROR: CAN'T EXECUTE SQL COMMAND: 
			${sql} 
			DESCRIPTION: 
			${sqlUtils.getLastError()} 
			ERROR KEY: 
			${sqlUtils.getLastKeyError()} 
		</#if>
		<#assign sql = 'select ef.*, e.code from app.entity e inner join app.entity_file ef on (e.entity_id=ef.entity_id) where e.code in(\'TEMP-0001\',\'TEMP-0002\',\'TEMP-0003\',\'TEMP-0004\',\'TEMP-0005\',\'TEMP-0006\',\'TEMP-0007\',\'TEMP-0008\')'>
		<#assign resultset = sqlUtils.executeSelect(sql)>
		<#if !(resultset??) >
			ERROR: CAN'T EXECUTE SQL COMMAND: 
			${sql}  
			DESCRIPTION: 
			${sqlUtils.getLastError()} 
			ERROR KEY: 
			${sqlUtils.getLastKeyError()} 
		<#else>
			<#list 1..size as c>
				<#if sqlUtils.moveNext(resultset)>
					<#assign sql = 'update app.entity_file set content=? where entity_file_id=?'>
					<#assign ps = sqlUtils.getPreparedStatement(sql)>
					<#if !(ps??) >
						ERROR: CAN'T EXECUTE SQL COMMAND: 
						${sql} 
						DESCRIPTION: 
						${sqlUtils.getLastError()} 
						ERROR KEY: 
						${sqlUtils.getLastKeyError()} 
					<#else>
						<#assign stream = sqlUtils.setFileFromResource(ps, 1, resultset.getString("code"))>				
						${ps.setLong(2, resultset.getLong("entity_file_id"))}
						<#assign updated = ps.executeUpdate()>
					</#if>
				</#if>
			</#list>
		</#if>

		<#if !sqlUtils.hasTable('SYSTEM_PROPERTY')>
				<#assign sql = 'create table app.system_property(
				       version bigint not null default 0
				       , created_by varchar(255) not null
				       , updated_by varchar(255) not null
				       , creation_date date default current_date
				       , change_date date default current_date
				       , system_property_id integer not null generated always as identity(start with 1, increment by 1)
				       , key_type_id smallint not null
				       , name varchar(100) not null
				       , enum_name_id integer
				       , list_name_id integer
					   , enum_value_id integer
					   , numeric_value numeric(14,4)
					   , varchar_value varchar(255)
					   , date_value date
					   , constraint uni_sysprod_name unique(name)
				       , constraint pk_sysprop primary key(system_property_id)
				       , constraint fk_sysprop_keytype foreign key(key_type_id) references app.key_type(key_type_id)
				       , constraint fk_sysprop_enumname foreign key(enum_name_id) references app.enum_name(enum_name_id)
				       , constraint fk_sysprop_listname foreign key(list_name_id) references app.list_name(list_name_id)
					)'>
			<#if !sqlUtils.execute(sql)>
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			<#else>
				<#assign sql = 'insert into app.system_property(created_by, updated_by, key_type_id, name, numeric_value)
				select \'remas\', \'remas\', key_type_id, \'VERSION MAJOR\', 1 from app.key_type where name = \'INTEGER\''>
				<#if (sqlUtils.executeUpdate(sql)<0) >
					ERROR: CAN'T EXECUTE SQL COMMAND: 
					${sql} 
					DESCRIPTION: 
					${sqlUtils.getLastError()} 
					ERROR KEY: 
					${sqlUtils.getLastKeyError()} 
				</#if>		  
				<#assign sql = 'insert into app.system_property(created_by, updated_by, key_type_id, name, numeric_value)
				select \'remas\', \'remas\', key_type_id, \'VERSION MINOR\', 0 from app.key_type where name = \'INTEGER\''>
				<#if (sqlUtils.executeUpdate(sql)<0) >
					ERROR: CAN'T EXECUTE SQL COMMAND: 
					${sql} 
					DESCRIPTION: 
					${sqlUtils.getLastError()} 
					ERROR KEY: 
					${sqlUtils.getLastKeyError()} 
				</#if>		  		  
				<#assign sql = 'insert into app.system_property(created_by, updated_by, key_type_id, name, numeric_value)
				select \'remas\', \'remas\', key_type_id, \'VERSION MICRO\', 1 from app.key_type where name = \'INTEGER\''>
				<#if (sqlUtils.executeUpdate(sql)<0) >
					ERROR: CAN'T EXECUTE SQL COMMAND: 
					${sql} 
					DESCRIPTION: 
					${sqlUtils.getLastError()} 
					ERROR KEY: 
					${sqlUtils.getLastKeyError()} 
				</#if>		  		 	
			</#if>	 
		</#if>
		<#if !sqlUtils.hasTable('SYSTEM_LIST_VALUE')>
				<#assign sql = 'create table app.system_list_value(
			       version bigint not null default 0
			       , created_by varchar(255) not null
			       , updated_by varchar(255) not null
			       , creation_date date default current_date
			       , change_date date default current_date
				   , system_list_value_id bigint not null generated always as identity(start with 1, increment by 1)
				   , list_value varchar(255)
				   , system_property_id integer not null
				   , constraint fk_syslistvalkeydef foreign key(system_property_id) references app.system_property(system_property_id)
				   , constraint fk_syslistval primary key(system_list_value_id)
				)'>
			<#if !sqlUtils.execute(sql)>
				ERROR: CAN'T EXECUTE SQL COMMAND: 
				${sql} 
				DESCRIPTION: 
				${sqlUtils.getLastError()} 
				ERROR KEY: 
				${sqlUtils.getLastKeyError()} 
			</#if>	 
		</#if>
		
</#if>
